﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using SpreadsheetGear;

namespace DataSync.DAL
{
    public class ExcelHelper
    {
        private static SpreadsheetGear.IWorkbook workBook = null;
        private static SpreadsheetGear.IWorksheet workSheet = null;
        private static SpreadsheetGear.IRange range = null;

        #region 成员变量
        private string templateFile;     //报表模板路径名称
        private string outputFile;       //导出报表路径名称
        private string sheetName;        //模板中worksheet名称

        public string TemplateFile
        {
            set { templateFile = value; }
        }
        public string OutputFile
        {
            set { outputFile = value; }
        }
        public string SheetName
        {
            set { sheetName = value; }
        }
        //public SpreadsheetGear.IWorkbook WorkBook
        //{
        //    get { return workBook; }
        //}
        //public SpreadsheetGear.IWorksheet WorkSheet
        //{
        //    get { return workSheet; }
        //}
        //public SpreadsheetGear.IRange Range
        //{
        //    get { return range; }
        //}
        #endregion

        #region 构造函数
        public ExcelHelper() { }
        public ExcelHelper(string filename, string outfilename, string sheetname)
        {
            if (filename.Trim() == "")
                throw new Exception("Excel模板文件路径不能为空！");
            if (outfilename.Trim() == "")
                throw new Exception("输出Excel文件路径不能为空！");
            if (sheetname.Trim() == "")
                throw new Exception("Excel模板工作簿名称不能为空！");
            if (!File.Exists(filename))
                throw new Exception("指定路径的Excel文件不存在！");

            TemplateFile = filename;
            OutputFile = outfilename;
            SheetName = sheetname;

            try
            {
                workBook = SpreadsheetGear.Factory.GetWorkbook(templateFile);
                //未能找到路径“D:\workspace\DataSync\TestResults\db2admin_ANDY 2010-08-02 16_40_29\ReportTemplate\速通卡发行情况统计.xls”的一部分。
                //未能找到文件“D:\workspace\DataSync\TestResults\速通卡发行情况统计.xls”。
                workSheet = workBook.Worksheets[sheetName];
                range = workSheet.Cells;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }
        #endregion

        #region 操作excel的方法
        /// <summary>  复制添加行
        /// 复制添加行
        /// </summary>
        /// <param name="cellNo">复制单元格的范围 "A5:F5"</param>   
        /// <param name="rows">行数</param>
        public void InsertRow(string cellNo, int rows)
        {
            SpreadsheetGear.IRange rowStart = range[cellNo];
            for (int i = 0; i < rows - 2; i++)
            {
                rowStart.Rows.Insert(SpreadsheetGear.InsertShiftDirection.Down);
            }
        }

        /// <summary>  插入数据
        /// 插入数据
        /// </summary>
        /// <param name="beginrow">开始行索引</param>
        /// <param name="dt">数据源</param>
        public void InsertDate(int beginrow, DataTable dt)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    range.Rows[beginrow + i, j].Value = dt.Rows[i][j].ToString();
                }
            }
        }

        /// <summary>  设置行高
        /// 设置行高
        /// </summary>
        public void SetRowHeight(int row, int cells)
        {
            range.Rows.RowHeight = row;
            range.Rows[0, 0].RowHeight = cells;
        }

        /// <summary>  给指定的单元格插入指定的值
        /// 给指定的单元格插入指定的值
        /// </summary>
        /// <param name="cells">单元格位置</param>
        /// <param name="value">插入的文本</param>
        public void SetCellsByText(string cells, string value)
        {
            string[] strArray = cells.Split(',');
            range.Rows[Convert.ToInt32(strArray[0]), Convert.ToInt32(strArray[1])].Value = value;
        }

        /// <summary> 给指定的单元格插入指定的值
        /// 给指定的单元格插入指定的值
        /// </summary>
        /// <param name="cells">二维数组 指定单元格和具体的值</param>
        public void SetCellsByText(string[,] cells)
        {
            for (int i = 0; i < cells.Length / 2; i++)
            {
                string str = cells[i, 0];
                string[] strArray = str.Split(',');
                range.Rows[Convert.ToInt32(strArray[0]), Convert.ToInt32(strArray[1])].Value = cells[i, 1];
            }
        }

        /// <summary>  删除指定的行
        /// 删除指定的行
        /// </summary>
        /// <param name="row">指定行  "5:5"</param>
        public void DeleteRow(string row, int rows)
        {
            if (rows < 2)
            {
                range.Rows[row].Delete();
            }
        }

        /// <summary>  保存生成的excel文件并返回一个二进制文件流
        /// 保存生成的excel文件并返回一个二进制文件流
        /// </summary>
        /// <returns>二进制文件流</returns>
        public byte[] SaveExcel()
        {
            try
            {
                TraceHelper.WriteLine(outputFile);
                workBook.SaveAs(outputFile, SpreadsheetGear.FileFormat.Excel8);
            }
            catch (Exception ex)
            {
                throw ex;
                //throw new Exception("将excel文件保存到本地服务器异常！");
            }

            try
            {
                return workBook.SaveToMemory(SpreadsheetGear.FileFormat.Excel8);
            }
            catch (Exception ex)
            {
                throw ex;
                //throw new Exception("将excel文件保存为二进制文件流异常！");
            }
        }
        #endregion

    }
}